﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for StudentDA
/// </summary>
public class StudentDA
{
    private DataAccess da;

	public StudentDA()
	{
		//
		// TODO: Add constructor logic here
		//
        da = new DataAccess();
	}
    public DataSet studentLogin(string username, string pass)
    {
        string query = "select * from Student where userName=@username and pass=@pass";
        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@username", SqlDbType.VarChar);
        sqlParameters[0].Value = Convert.ToString(username);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.VarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        DataSet ds= da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public bool insertDesign(Design d,byte[] images)
    {
        string name = d.Name;
        string postDate = d.PostDate;
        string description = d.Description;
        string quotation = d.Quotation;
        int studentId = d.St.Id;
        int comId = d.Com.ComId;
        string query = "insert into Design(name,postDate,[description],quotation,images,studentId,comId) values(@name,@postDate,@description,@quotation,@images,@studentId,@comId)";
        SqlParameter[] sqlParameters = new SqlParameter[7];
        sqlParameters[0] = new SqlParameter("@name", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(name);
        sqlParameters[1] = new SqlParameter("@postDate", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(postDate);
        sqlParameters[2] = new SqlParameter("@description", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(description);
        sqlParameters[3] = new SqlParameter("@quotation", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(quotation);
        sqlParameters[4] = new SqlParameter("@images", SqlDbType.Image);
        sqlParameters[4].Value = images;
        sqlParameters[5] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[5].Value = Convert.ToString(studentId);
        sqlParameters[6] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[6].Value = Convert.ToString(comId);

        return da.executeInsertQuery(query, sqlParameters);
    }

    public bool updateDesign(Design d)
    {
        int designId = d.Id;
        string name = d.Name;
        string postDate = d.PostDate;
        string description = d.Description;
        string quotation = d.Quotation;
        int studentId = d.St.Id;
        int comId = d.Com.ComId;

        string query = "update Competition set name=@name,description=@description,quotation=@quotation where designId=@designId";

        SqlParameter[] sqlParameters = new SqlParameter[4];
        sqlParameters[0] = new SqlParameter("@name", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(name);
        sqlParameters[1] = new SqlParameter("@description", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(description);
        sqlParameters[2] = new SqlParameter("@quotation", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(quotation);
        sqlParameters[3] = new SqlParameter("@designId", SqlDbType.BigInt);
        sqlParameters[3].Value = Convert.ToString(designId);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delDesign(Design d)
    {
        int designId = d.Id;

        string query = "delete Design where designId=@designId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@designId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(designId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllDesign(int studentId)
    {
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,c.name as comName from Design d inner join Competition c on d.comId=c.comId inner join Student st on d.studentId=st.studentId where st.studentId=@studentId";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.Int);
        sqlParameters[0].Value =studentId;
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet getDesignByComp(int studentId,string compName)
    {
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,c.name as comName from Design d inner join Competition c on d.comId=c.comId inner join Student st on d.studentId=st.studentId where st.studentId=@studentId and c.name like '%"+compName+"%'";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.Int);
        sqlParameters[0].Value = studentId;
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet getDesignByName(int studentId, string name)
    {
        DataSet ds = new DataSet();
        string query = "select d.designId,d.name,d.postDate,c.name as comName from Design d inner join Competition c on d.comId=c.comId inner join Student st on d.studentId=st.studentId where st.studentId=@studentId and d.name like '%" + name + "%'";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.Int);
        sqlParameters[0].Value = studentId;
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

}